Stored Procedures

Stored Procedures

< > Connection Statements Query Statements Advanced Statements Browse Statements Table of Contents

Stored procedures are added to a data source in a data source-dependent manner. Please see the documentation for the particular DBMS product for information on adding a stored procedure to the data source.

Execution of a stored procedure can be prepared with the SQL PREPARE QUERY statement and executed with the SQL START QUERY statement if the data source supports stored procedures and the driver supports the ODBC syntax for procedure invocations. The ODBC syntax for procedure invocation is as follows:

{[?=]call ProcedureName [([parameter][,[parameter]]…)]}

where the braces ({}) and parentheses are literally part of the syntax and the brackets ([]) are metacharacters used to indicate optional elements of the syntax.

A procedure can have zero or more parameters. It can also return a value, as indicated by the optional parameter marker ?= at the start of the syntax. If a parameter is an input parameter or an input/output parameter, it can be a literal or a parameter marker. However, interoperable applications should always use parameter markers, because some data sources do not accept literal parameter values. If a parameter is an output parameter, it must be a parameter marker. Parameter markers must be bound to COBOL data items with the SQL BIND PARAMETER statement after the procedure call statement is prepared and before the procedure call statement is started.

Input and input/output parameters can be omitted from procedure calls. If a procedure is called with parentheses but without any parameters, such as {call procedure-name()}, the driver instructs the data source to use the default value for the first parameter. If the procedure does not have any parameters, this may cause the procedure to fail. If a procedure is called without parentheses, such as {call ProcedureName}, the driver does not send any parameter values.

Literal values can be specified for input and input/output parameters in procedure calls. For example, suppose the procedure InsertOrder has five input parameters. The following call to InsertOrder omits the first parameter, provides a literal value for the second parameter, and uses a parameter marker for the third, fourth, and fifth parameters:

{call InsertOrder(, 10, ?, ?, ?)} // Not interoperable because of literal param!

Note If a parameter is omitted, the comma delimiting it from other parameters must still appear. If an input or input/output parameter is omitted, the procedure uses the default value of the parameter. Another way to specify the default value of an input or input/output parameter is to set the value of the length indicator data item bound to the parameter to sql-Default-Param.

If an input/output parameter is omitted or if a literal is supplied for the parameter, the driver discards the output value. Similarly, if the parameter marker for the return value of a procedure is omitted, the driver discards the return value. Finally, if an application specifies a return value parameter for a procedure that does not return a value, the driver sets the value of the length indicator data item bound to the parameter to sql-Null-Data.

The following example calls the GetCostHours procedure on the Payroll data source to return the number of costed hours for DeptCode 20:

SQL CONNECT DATASOURCE sql-ConnectionHandle
    "Payroll" "MyName" ws-MyPassword.
SQL PREPARE QUERY sql-QueryHandle
    sql-ConnectionHandle
    "{? = call GetCostHours(?)}".
SQL BIND PARAMETER sql-QueryHandle
    1 sql-Integer sql-Param-Output ws-costhours OMITTED
    2 sql-Integer sql-Param-Input ws-deptcode OMITTED.
MOVE 20 TO ws-deptcode.
SQL START QUERY sql-QueryHandle.
SQL FETCH ROW sql-QueryHandle.
DISPLAY "Cost Hours for Dept " ws-deptcode
    " = " ws-costhours.
SQL END QUERY sql-QueryHandle.
SQL DISCONNECT sql-ConnectionHandle.

Procedures may create a result set, but are not required to create a result set. It is data source ‑ dependent whether output parameter values are valid before all rows of the result set, if any, have been fetched. The SQL FETCH ROW statement normally causes parameter output values to be transferred to the bound COBOL data items only when setting sql-EndOfData to true. The InstantSQL configuration option GetOutputParamsEachRow can be set to "True" to cause the SQL FETCH ROW statement to transfer parameter output values each time the statement is executed. The InstantSQL configuration option GetOutputParamsOnStart can be set to "True" to cause the SQL START QUERY statement to transfer parameter output values each time the statement is executed.

To determine if a data source supports procedures, an application can use the SQL DESCRIBE CONNECTION statement with an extended connection description group. The elementary data item named sql-ConProcedures in the extended connection description group will then have the value "Y" if the data source supports procedures and the driver supports the ODBC procedure invocation syntax; otherwise, its value will be "N".

Information about procedures available in a data source can be obtained with the SQL QUERY PROCEDURES statement.

Information about the parameters and columns of an available procedure can be obtained with the SQL QUERY PROCEDURE-COLUMNS statement.